-- Script on modify active column
-- The initial value of 'Active' is null , set all of active to 0
UPDATE [datalogger].[dbo].[DL_AFETest400] SET [datalogger].[dbo].[DL_AFETest400].[Active]=0;

-- Gruop by AFE400RecordID and AFETaskName, Get all id data which the value of UTCCreationTime is the latest time in each group
PRINT 'FECH DATA'
DECLARE CursorAfeJoin CURSOR SCROLL FOR
SELECT RecordID from [datalogger].[dbo].[DL_AFETest400] afe inner join 
(
SELECT [AFE400RecordID]
      ,[AFETaskName]
	  ,MAX([UTCCreationTime]) utime
  FROM [datalogger].[dbo].[DL_AFETest400]
  GROUP BY AFE400RecordID,AFETaskName
) afe_group 
on afe.AFE400RecordID = afe_group.AFE400RecordID 
and afe.AFETaskName = afe_group.AFETaskName
and afe.UTCCreationTime = afe_group.utime

-- update active column
PRINT 'start to up date active data'
OPEN CursorAfeJoin
DECLARE @id INT
FETCH NEXT FROM CursorAfeJoin INTO @id
WHILE @@FETCH_STATUS = 0 
BEGIN
	PRINT 'Update active where Record id is ' + convert(varchar(20),@id)
	UPDATE [datalogger].[dbo].[DL_AFETest400] SET [Active] = 1 WHERE RecordID = @id
	FETCH NEXT FROM CursorAfeJoin INTO @id
END
CLOSE CursorAfeJoin
DEALLOCATE CursorAfeJoin